package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.JOptionPane;
import javax.swing.UIManager;
import outros.Conexao;
import transporte.TOEmprestimo;
import transporte.TOPatrimonio;

public class DAOEmprestimo {
    private Conexao conexao = new Conexao();
    private Connection con = null;

    public boolean abrirConexao() {
        UIManager.put("OptionPane.yesButtonText", "Sim");
        UIManager.put("OptionPane.noButtonText", "Não");
        UIManager.put("OptionPane.cancelButtonText", "Cancelar");
        try {
            conexao.abrirConexao();
            con = conexao.getConexao();
            return true;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Erro ao tentar conectar ao banco de dados.");
            return false;
        }
    }

    public String criarRegistro(ArrayList<TOEmprestimo> emp, Integer tipo) {
        PreparedStatement ps = null;
        String sql = "INSERT INTO patri_reg_emprestimo(numero, data, hora, "+
                     "usuario, responsavel, tipo, obs, setor_responsavel, "+
                     "empresa, endereco, cnpj, telefone, bem_orgao, bem_area, "+
                     "bem_grupo, bem_subgrupo, bem_numero) VALUES(?,?,?,?,?,?,?,"+
                     "?,?,?,?,?,?,?,?,?,?)";
        try {
            abrirConexao();
            int id = getProximo();
            if (id!=0) {
                for (int i=0;i<emp.size();i+=1) {
                    ps = con.prepareStatement(sql);
                    ps.setInt(1, id);
                    ps.setDate(2, new java.sql.Date(emp.get(i).getData().getTime()));
                    ps.setString(3, emp.get(i).getHora());
                    ps.setInt(4, emp.get(i).getUsuario());
                    ps.setString(5, emp.get(i).getResponsavel());
                    ps.setInt(6, emp.get(i).getTipo());
                    mudarEmprestimo(emp.get(i).getPatrimonio(), emp.get(i).getTipo());
                    if (tipo==0) {
                        ps.setString(7, emp.get(i).getObs());
                        try {
                            ps.setInt(8, emp.get(i).getSetorResponsavel());
                        } catch (Exception e) {
                            ps.setInt(8, -1);
                        }
                        ps.setString(9, "");
                        ps.setString(10, "");
                        ps.setString(11, "");
                        ps.setString(12, "");
                    } else {
                        ps.setString(7, "");
                        ps.setInt(8, -1);
                        ps.setString(9, emp.get(i).getEmpresa());
                        ps.setString(10, emp.get(i).getEndereco());
                        ps.setString(11, emp.get(i).getCnpj());
                        ps.setString(12, emp.get(i).getTelefone());
                    }
                    ps.setInt(13, emp.get(i).getPatrimonio().getOrgao());
                    ps.setInt(14, emp.get(i).getPatrimonio().getArea());
                    ps.setInt(15, emp.get(i).getPatrimonio().getGrupo());
                    ps.setInt(16, emp.get(i).getPatrimonio().getSubgrupo());
                    ps.setInt(17, emp.get(i).getPatrimonio().getNumero());
                    ps.execute();
                }
                con.commit();
                conexao.fecharConexao();
                return "";
            } else {
                conexao.fecharConexao();
                JOptionPane.showMessageDialog(null, "Erro ao tentar atribuir novo ID.");
                return "erro";
            }
        } catch (Exception e) {
            conexao.fecharConexao();
            JOptionPane.showMessageDialog(null, "Erro ao tentar inserir registro. Detalhes: "+e.toString());
            return "erro";
        }
    }

    private void mudarEmprestimo(TOPatrimonio pat, int tipo) {
        PreparedStatement ps = null;
        String sql = "UPDATE patri_patrimonio SET emprestimo=? WHERE orgao=? AND area=? AND "+
                     "grupo=? AND subgrupo=? AND numero=?";
        try {
            abrirConexao();
            ps = con.prepareStatement(sql);
            ps.setInt(1, tipo);
            ps.setInt(2, pat.getOrgao());
            ps.setInt(3, pat.getArea());
            ps.setInt(4, pat.getGrupo());
            ps.setInt(5, pat.getSubgrupo());
            ps.setInt(6, pat.getNumero());
            ps.execute();
            con.commit();
            conexao.fecharConexao();
        } catch (Exception e) {
            conexao.fecharConexao();
            JOptionPane.showMessageDialog(null, "Erro ao tentar mudar o empréstimo. Detalhes: "+e.toString());
        }
    }

    private int getProximo(){
        String sql = "SELECT MAX(numero) FROM patri_reg_emprestimo";
        try {
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = st.executeQuery(sql);
            if (rs.first()){
                return rs.getInt(1)+1;
            } else {
                return 0;
            }
        } catch (Exception e){
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro", JOptionPane.ERROR_MESSAGE);
            return 0;
        }
    }
}
